﻿using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Net.Mail;
//using System.Web.Mail;

namespace DHYHN.ASPNET.HoiNghi
{
    public class Business
    {
        DataAcess data = new DataAcess();

        public bool CheckLogin(string user, string pass, out string quyen)
        {
            string sql = "select * from tbUsers where userid='" + user + "' AND password='" + pass + "' AND status Like N'open'";
            if (data.hasValue(sql))
            {
                //quyen=

                DataTable dt = data.getDataTable(sql);
                quyen = Convert.ToString(dt.Rows[0].ItemArray.GetValue(3)).Trim();
                return true;
            }
            else
            {
                quyen = "";
                return false;
            }
            //return data.hasValue(sql);
        }

        public void Insert_User(string userID, string Pass, string email, string quyenhan, string trangthai)
        {
            string sql = "insert into tbNguoiDung values('" + userID + "','" + Pass + "','" + email + "',N'" + quyenhan + "',N'" + trangthai + "')";
            data.ThucThiSql(sql);
        }

        public void Update_User(string userID, string Pass, string email, string quyenhan, string trangthai)
        {
            string sql = "update tbNguoiDung set PassWord='" + Pass + "',Email='" + email + "', Pemission=N'" + quyenhan + "',Status=N'" + trangthai + "' where UserName='" + userID + "'";
            data.ThucThiSql(sql);
        }

        public void Detele_User(string userID)
        {
            string sql = "delete tbtbNguoiDung where UserName='" + userID + "'";
            data.ThucThiSql(sql);
        }



        public void InsertReport(string userid
           ,string title
           ,string description
           ,string docfileURL
           ,string slidefileURL
           ,string filesize
           ,string created
           ,string status)
 
        {
            string sql = "INSERT INTO tbResports ([userid],[title],[description],[docfileURL],[slidefileURL],[filesize],[created],[status]) VALUES " +
           "('" + userid + "',N'" + title + "',N'" + description + "',N'" + docfileURL + "',N'" + slidefileURL + "'," + filesize + ",'" + created + "',N'" + status + "')";

            DataAcess data = new DataAcess();
            data.ThucThiSql(sql);
        }




        public void DoiTuong_Insert(string TenDT, string NguoiTao, string NgayTao, string GhiChu)
        {
            string commadSQL = " INSERT INTO [tbDoiTuong] " +
           " ([TenDT]" +
           " ,[NguoiTao]" +
           " ,[NgayTao]" +
           " , [GhiChu] )" +
           " VALUES " +
           "(N'" + TenDT + "',N'" + NguoiTao + "','" + NgayTao + "',N'" + GhiChu + "')";
            DataAcess data = new DataAcess();
            data.ThucThiSql(commadSQL);
        }
        public void DoiTuong_Update(string MaDT, string TenDT, string NguoiTao, string NgayTao, string GhiChu)
        {
            string commandSQL = "UPDATE [tbDoiTuong] " +
                              " SET [TenDT] =N'" + TenDT + "'" +
                              " ,[NguoiTao] =N'" + NguoiTao + "'" +
                              " ,[NgayTao] ='" + NgayTao + "'" +
                              " ,[GhiChu] =N'" + GhiChu + "'" +
                              " WHERE MaDT=" + MaDT;
            DataAcess data = new DataAcess();
            data.ThucThiSql(commandSQL);
        }

        public void DoiTuong_Delete(string MaDT)
        {
            MonThi_Delete_MaDT(MaDT);
            string commandSQL = "	DELETE FROM [tbDoiTuong] WHERE MaDT=" + MaDT;
            DataAcess data = new DataAcess();
            data.ThucThiSql(commandSQL);
        }

        public void MonThi_Insert(string TenMT, string MaDT, string TGThi, string SLCauHoi, string NgayThi, string NguoiTao, string NgayTao, string GhiChu)
        {
            string commandSQL = "INSERT INTO [tbMonThi] " +
           "([TenMT],[MaDT],[TGThi],[SLCauHoi],[NgayThi],[NguoiTao],[NgayTao],[GhiChu]) " +
            "VALUES (N'" + TenMT + "'," + MaDT + ",'" + TGThi + "'," + SLCauHoi + ",'" + NgayThi + "',N'" + NguoiTao + "','" + NgayTao + "',N'" + GhiChu + "')";
            DataAcess data = new DataAcess();

            data.ThucThiSql(commandSQL);
        }

        public void MonThi_Update(string MaMT, string TenMT, string MaDT, string TGThi, string SLCauHoi, string NgayThi, string NguoiTao, string NgayTao, string GhiChu)
        {
            string commandSQL = "UPDATE [tbMonThi] " +
                    "SET [TenMT] ='" + TenMT + "'" +
                      ",[MaDT] = " + MaDT +
                      ",[TGThi] = '" + TGThi + "'" +
                      ",[SLCauHoi] = " + SLCauHoi +
                      ",[NgayThi] = '" + NgayThi + "'" +
                      ",[NguoiTao] = '" + NguoiTao + "'" +
                      ",[NgayTao] = '" + NgayTao + "'" +
                      ",[GhiChu] = '" + GhiChu + "'" +
                      " WHERE MaMT=" + MaMT;
            DataAcess data = new DataAcess();
            data.ThucThiSql(commandSQL);
        }

        public void MonThi_Delete(string MaMT)
        {
            CauHoi_Delete_MaMT(MaMT);
            string commandSQL = "	DELETE FROM [tbMonThi] WHERE MaMT=" + MaMT;
            DataAcess data = new DataAcess();
            data.ThucThiSql(commandSQL);
        }


        public void MonThi_Delete_MaDT(string MaDT)
        {

            string commandSQL = "	DELETE FROM [tbMonThi] WHERE MaDT=" + MaDT;
            DataAcess data = new DataAcess();
            data.ThucThiSql(commandSQL);
        }

        public void CauHoi_Insert(string MaMT, string NoiDung, string Nhom, string NguoiTao, string NgayTao, string GhiChu, string TenCH)
        {
            string commandSQL = "INSERT INTO tbCauHoi " +
           "([MaMT],[NoiDung],[Nhom],[NguoiTao],[NgayTao],[GhiChu],[TenCH]) " +
           " VALUES(" + MaMT + ",N'" + NoiDung + "'," + Nhom + ",N'" + NguoiTao + "','" + NgayTao + "',N'" + GhiChu + "',N'" + TenCH + "')";
            DataAcess data = new DataAcess();
            data.ThucThiSql(commandSQL);
        }
        public void CauHoi_Update(string MaCH, string MaMT, string NoiDung, string Nhom, string NguoiTao, string NgayTao, string GhiChu, string TenCH)
        {
            string commandSQL = "UPDATE [tbCauHoi]" +
            "SET [MaMT] = " + MaMT +
              ",[NoiDung] = N'" + NoiDung + "'" +
              ",[Nhom] = '" + Nhom + "'" +
              ",[NguoiTao] = N'" + NguoiTao + "'" +
              ",[NgayTao] = '" + NgayTao + "'" +
              ",[GhiChu] = N'" + GhiChu + "'" +
              ",[TenCH] = N'" + TenCH + "'" +
         " WHERE MaCH=" + MaCH;
            DataAcess data = new DataAcess();
            data.ThucThiSql(commandSQL);
        }
        public void CauHoi_Delete(string MaCH)
        {
            string commandSQL = "DELETE FROM [tbCauHoi] WHERE MaCH=" + MaCH;
            DataAcess data = new DataAcess();
            data.ThucThiSql(commandSQL);
        }

        public void CauHoi_Delete_MaMT(string MaMT)
        {
            string commandSQL = "DELETE FROM [tbCauHoi] WHERE MaMT=" + MaMT;
            DataAcess data = new DataAcess();
            data.ThucThiSql(commandSQL);
        }
        public void DeThi_Insert(string FileURD, string MaMT, string NguoiTao, string NgayTao)
        {
            string commandSQL = " INSERT INTO [tbDeThi] " +
            " ([FileURD],[MaMT],[NguoiTao],[NgayTao]) " +
            " VALUES ('" + FileURD + "'," + MaMT + ",'" + NguoiTao + "','" + NgayTao + "')";
            DataAcess data = new DataAcess();
            data.ThucThiSql(commandSQL);
        }

        public void DeThi_Delete(string MaDT)
        {
            string commandSQL = "DELETE FROM [tbDeThi] WHERE MaDT=" + MaDT;
            DataAcess data = new DataAcess();
            data.ThucThiSql(commandSQL);
        }


        public static bool validateInput(string text)
        {
            string[] values = new string[] { "'" };
            bool ketqua = true;
            foreach (string s in values)
            {
                if (text.IndexOf(s) >= 0)
                    ketqua = false;
            }

            return ketqua;
        }



        public DataTable GetReport_With_ID(string ReportID)
        {
            string SelectCommand="SELECT tbResports.reportid, tbResports.userid, tbResports.title, tbResports.description, tbResports.docfileURL, tbResports.slidefileURL, tbResports.filesize, tbResports.created, tbResports.status, tbUsers.userid AS Expr1, tbUsers.password, tbUsers.pemission, tbUsers.status AS Expr2, tbUsers.firstName, tbUsers.lastName, tbUsers.birthday, tbUsers.sex, tbUsers.address, tbUsers.career, tbUsers.nationality FROM tbResports INNER JOIN tbUsers ON tbResports.userid = tbUsers.userid where tbResports.reportid="+ReportID;
            DataAcess data = new DataAcess();
            return data.getDataTable(SelectCommand);
        }

        public DataTable GetUser_With_ID(string UserName)
        {
            string sql = "SELECT * from tbNguoiDung where UserName='" + UserName + "'";
            DataAcess data = new DataAcess();
            return data.getDataTable(sql); ;
        }

        public DataTable GetMonThi_With_ID(string MaMT)
        {
            string sql = "SELECT [MaMT],[TenMT],[MaDT],[TGThi],[SLCauHoi],[NgayThi],[NguoiTao],[NgayTao],[GhiChu]  FROM [tbMonThi] WHERE MaMT=" + MaMT;
            DataAcess data = new DataAcess();
            return data.getDataTable(sql);
        }


        public DataTable GetCauHoi_With_ID(string MaCH)
        {
            string sql = "SELECT tbCauHoi.MaCH, tbCauHoi.MaMT, tbCauHoi.NoiDung, tbCauHoi.Nhom, tbCauHoi.NguoiTao, tbCauHoi.NgayTao, tbCauHoi.GhiChu, tbCauHoi.TenCH, tbDoiTuong.MaDT, tbDoiTuong.TenDT, tbMonThi.TenMT, tbMonThi.MaMT AS Expr1 FROM tbCauHoi INNER JOIN tbMonThi ON tbCauHoi.MaMT = tbMonThi.MaMT INNER JOIN tbDoiTuong ON tbMonThi.MaDT = tbDoiTuong.MaDT WHERE (tbCauHoi.MaCH =" + MaCH + ")";
            DataAcess data = new DataAcess();
            return data.getDataTable(sql);
        }


        public static bool CheckNumber(string number)
        {
            bool ketqua = true;
            try
            {
                int.Parse(number);
            }

            catch
            {
                ketqua = false;
            }
            return ketqua;
        }

        public DataTable GetCauHoi_byMonThiID(string maMonThi, string nhom)
        {
            string sql = "SELECT  MaCH, TenCH,NoiDung,Nhom from tbCauHoi WHERE (MaMT =" + maMonThi + " and Nhom=" + nhom + " )";
            DataAcess data = new DataAcess();
            return data.getDataTable(sql);
        }

        public static string ConvertDateTime(string dateData)
        {
            string[] dateArray = dateData.Split('/');
            string kq = "";
            if (dateArray.Length == 3)
            {
                kq = string.Format("{0}/{1}/{2}", dateArray[1].Trim(), dateArray[0].Trim(), dateArray[2].Trim());

                try
                {
                    DateTime dt = DateTime.Parse(kq);
                }
                catch
                {
                    kq = "";
                }
            }
            return kq;
        }


        //public void get

        public static bool SendMail(string Mail_From, string Pass_From, string Mail_To, string ConTent, string subject)
        {
            bool ok = true;
            MailMessage mail = new MailMessage();
            SmtpClient SmtpServer = new SmtpClient("smtp.gmail.com");
            mail.From = new MailAddress(Mail_From);
            mail.To.Add(Mail_To);
            mail.Subject = subject;
            mail.Body = ConTent;
            mail.IsBodyHtml = true;
            SmtpServer.Port = 587;
            SmtpServer.Credentials = new System.Net.NetworkCredential(Mail_From, Pass_From);
            SmtpServer.EnableSsl = true;

            try
            {
                SmtpServer.Send(mail);
            }
            catch
            {
                try
                {
                    SmtpServer.Port = 465;
                    SmtpServer.Send(mail);
                }
                catch
                {
                    ok = false;
                }
            }
            return ok;
        }
        public void SaoLuDL()
        {
            string sqlCommand = @"BACKUP DATABASE QuanLyDeThi TO DISK='D:\QuanLyDeThi_FULL_BACKUP.BAK'";
            DataAcess da = new DataAcess();
            da.ThucThiSql(sqlCommand);
        }

    }
}
